-- @owner: Alice
-- @date: 2025-09-03
-- @testpoint: 确定性函数支持result_cache,函数入参为smallint、varchar，合理报错
@conn PrimaryDbAmode;
drop extension if exists gms_output;
drop table if exists all_support_type;
drop table if exists all_support_type_temp;
drop function if exists func_smallint;
drop function if exists func_smallint_cache;
drop function if exists func_smallint_nopara;
drop function if exists func_smallint_cache;
drop function if exists func_varchar_cache;
drop function if exists func_int_cache;
drop function if exists func_bigint_cache;
drop function if exists func_number_cache;
drop function if exists func_numeric_cache;
drop function if exists func_float_cache;
drop function if exists func_double_precision_cache;
drop function if exists func_char_cache;
drop function if exists func_varchar2_cache;
drop function if exists func_nvarchar2_cache;
drop function if exists func_text_cache;
drop function if exists func_bool_cache;
drop function if exists func_timestamp_cache;
drop function if exists func_smallint_cache;
drop function if exists taxi_accum ;
drop function if exists pg_concat;
drop function if exists pg_concat_fin;
drop function if exists para_cache ;
drop function if exists para_cache ;
drop function if exists var_test1;
drop function if exists func_smallint_cache;
drop function if exists tran_update_func;
drop function if exists factorial(integer);
drop function if exists f_test;
drop AGGREGATE if exists pg_concat(text) cascade;
drop AGGREGATE if exists pg_concat(text,text) cascade;
drop function if exists pg_concat;
drop function if exists pg_concat_fin;
create extension gms_output;
--result_cache
--会话需要设置这个值会输出debug信息
set enable_func_cache = 1;
select gms_output.enable();
set query_dop=1;
set datestyle='ISO,MDY';
--创建表及函数
drop table if exists all_support_type;
create table all_support_type(
id int,
col_bool bool,
col_smallint smallint,
col_int int,
col_bigint bigint,
col_float float,
col_double_precision double precision,
col_char char(720),
col_varchar varchar(920),
col_varchar2 varchar2(20),
col_timestamp timestamp,
col_numeric numeric,
col_number number,
col_text text
);
insert into all_support_type values( 1, 'f',1,1,1,1,1,'1_haha','0_haha','1_haha','2024-11-22 16:36:45',1,1,'0_haha');
insert into all_support_type values( 2, 'f',2,2,2,2,2,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values( 3, 'f',0,0,0,0,0,'0_haha','1_haha','0_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values( 4, 'f',1,1,1,1,1,'1_haha','1_haha','1_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values( 5, 'f',2,2,2,2,2,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values( 6, 'f',0,0,0,0,0,'0_haha','1_haha','0_haha','2024-11-22 16:36:45',0,0,'1_haha');
insert into all_support_type values( 7, 'f',1,1,1,1,1,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values( 8, 'f',2,2,2,2,2,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values( 9, 'f',0,0,0,0,0,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(10, 'f',1,1,1,1,1,'0_haha','1_haha','0_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values(11, 'f',2,2,2,2,2,'0_haha','0_haha','0_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values(12, 'f',0,0,0,0,0,'0_haha','0_haha','0_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(13, 'f',1,1,1,1,1,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',1,1,'0_haha');
insert into all_support_type values(14, 'f',2,2,2,2,2,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',2,2,'1_haha');
insert into all_support_type values(15, 'f',0,0,0,0,0,'1_haha','0_haha','1_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(16, 'f',1,1,1,1,1,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values(17, 'f',2,2,2,2,2,'1_haha','0_haha','0_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values(18, 'f',0,0,0,0,0,'1_haha','1_haha','1_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(19, 'f',1,1,1,1,1,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',1,1,'0_haha');
insert into all_support_type values(20, 'f',2,2,2,2,2,'1_haha','1_haha','0_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type(id) values(21);
insert into all_support_type(id) values(22);
insert into all_support_type values(23,'t',1111,1111,1111,1111,1111,'aasddffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff','aaaaaa','fffff','1997-1-1 10:00:00',1,1,'3345');
insert into all_support_type values(24,'',1111,1111,1111,1111,1111,'','','','',1,1,'');
select count(*) from (select * from all_support_type order by id) ;
select * into all_support_type_temp from all_support_type;
insert into all_support_type(id,col_varchar) values(11,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1');
insert into all_support_type(id,col_varchar) values(12,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1');
insert into all_support_type(id,col_varchar) values(100,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1');
insert into all_support_type(id,col_varchar) values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1');
--bool
create or replace function func_bool_cache(p bool) returns varchar as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return 'aa';
end;
$$ language plpgsql stable result_cache;
/
select func_bool_cache(12) from (select * from all_support_type order by id) ;
select col_bool,func_bool_cache(col_bool) from (select * from all_support_type order by id) ;
--timestamp
create or replace function func_timestamp_cache(p timestamp) returns varchar as $$
begin
raise notice 'NOTICE: excute function...';
if p = '2024-11-22 16:36:45' then
gms_output.put_line('param:'||p);
end if;
return 'aa';
end;
$$ language plpgsql stable result_cache;
/
select func_timestamp_cache('2001-1-1') from (select * from all_support_type order by id) ;
select col_timestamp,func_timestamp_cache(col_timestamp) from (select * from all_support_type order by id) ;

--开启并行查询
create or replace function func_smallint_cache(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql stable result_cache;
/
select func_smallint_cache(col_smallint) from (select * from all_support_type order by id) ;
SET query_dop = 4;
select func_smallint_cache(col_smallint) from (select * from all_support_type order by id) ;
SET query_dop = 1;
select func_smallint_cache(col_smallint) from (select * from all_support_type order by id) ;
\parallel on 100
select func_smallint_cache(col_smallint) from (select * from all_support_type order by id) ;
\parallel off
set max_parallel_workers_per_gather=10;
select func_smallint_cache(col_smallint) from (select * from all_support_type order by id) ;
select func_smallint_cache(col_smallint) from (select * from all_support_type order by id) ;
--窗口函数
drop function if exists taxi_accum;
create or replace function taxi_accum (p1 bigint, p2 bigint, p3 bigint)
RETURNS bigint AS
$$
declare
sum bigint=0;
BEGIN
raise notice 'NOTICE: execute function...';
sum=p1+p2+p3;
RETURN 12;
END;
$$
LANGUAGE plpgsql window result_cache stable;
/
select taxi_accum(1,3,2) over (partition by col_varchar) from (select * from all_support_type order by id) ;
select taxi_accum(col_smallint,col_int,col_bigint) over (partition by col_varchar) from (select * from all_support_type order by id) ;
--聚集函数
create or replace function pg_concat( text, text ) returns text as
$BODY$
begin
raise notice 'NOTICE: execute function pg_concat...';
if $1 is null then
return $2;
else
return $1 || $2;
end if;
end;
$BODY$ language plpgsql result_cache stable;
/
create or replace function pg_concat_fin(text) returns text
as
$$
begin
raise notice 'NOTICE: execute function pg_concat_fin...';
return substr($1,1,length($1)-1);
end;
$$
language plpgsql result_cache stable;
/
create aggregate pg_concat (
basetype = text,
sfunc = pg_concat,
stype = text,
finalfunc = pg_concat_fin);
select id,pg_concat(col_text||',') from (select * from all_support_type order by id,col_text) group by id;
--可变参数过多
--16个
drop function if exists para_cache;
create or replace function para_cache (p1 bigint,p2 bigint,p3 bigint,p4 bigint,p5 bigint,p6 bigint,p7 bigint,p8 bigint,p9 bigint,p10 bigint,p11 bigint,p12 bigint,p13 bigint,p14 bigint,p15 bigint,p16 bigint)
RETURNS bigint AS
$$
BEGIN
raise notice 'NOTICE: execute function...';
RETURN 12;
END;
$$
LANGUAGE plpgsql result_cache stable;
/
select para_cache(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)  from (select * from all_support_type order by id) ;
select para_cache(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,col_bigint) from (select * from all_support_type order by id) ;
--多于16个
--16个
drop function if exists para_cache;
create or replace function para_cache (p1 bigint,p2 bigint,p3 bigint,p4 bigint,p5 bigint,p6 bigint,p7 bigint,p8 bigint,p9 bigint,p10 bigint,p11 bigint,p12 bigint,p13 bigint,p14 bigint,p15 bigint,p16 bigint,p17 bigint)
RETURNS bigint AS
$$
BEGIN
raise notice 'NOTICE: execute function...';
RETURN 12;
END;
$$
LANGUAGE plpgsql result_cache stable;
/
select para_cache(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17)  from (select * from all_support_type order by id) ;
select para_cache(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,col_bigint) from (select * from all_support_type order by id) ;
--可变参数
create or replace function var_test1(VARIADIC arr bigint[])
returns bigint
AS $$
DECLARE
sum bigint=0;
BEGIN
raise notice 'NOTICE: execute function ....';
for i in 1..array_length(arr,1) loop
sum=sum+arr[i];
end loop;
return sum;
END;
$$
LANGUAGE plpgsql;
/
select var_test1(2,34,55,66) from (select * from all_support_type order by id) ;
select var_test1(2,34,55,col_bigint) from (select * from all_support_type order by id) ;
--select之外的语句
create or replace function func_smallint_cache(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql stable result_cache;
/
--explain(costs off,analyze,timing off)
--select func_smallint_cache(col_smallint) from (select * from all_support_type order by id);
--explain(costs off,analyze,timing off)
delete from (select * from all_support_type order by id) where func_smallint_cache(col_smallint)<2;
--explain(costs off,analyze,timing off)
update all_support_type set id=1000 where func_smallint_cache(col_smallint)<2;
--case20
--函数中包含自治事务
--创建表及函数
drop table if exists all_support_type;
create table all_support_type(
id int,
col_bool bool,
col_smallint smallint,
col_int int,
col_bigint bigint,
col_float float,
col_double_precision double precision,
col_char char(820),
col_varchar varchar(920),
col_varchar2 varchar2(20),
col_timestamp timestamp,
col_numeric numeric,
col_number number,
col_text text
);
insert into all_support_type select * from all_support_type_temp;
select count(*) from (select * from all_support_type order by id) ;
create or replace function tran_update_func(p1 int) RETURNS int AS
$$
DECLARE
num int=0;
PRAGMA AUTONOMOUS_TRANSACTION;
begin
raise notice 'NOTICE: execute function...';
select count(*) into num from  all_support_type where id=23;
RETURN 12;
END
$$ LANGUAGE PLPGSQL result_cache stable;
/
--explain(costs off,analyze,timing off)
--select tran_update_func(col_int) from (select * from all_support_type order by id) ;
--递归函数
create or replace function factorial (
  n int
) RETURN int immutable result_cache
  AUTHID DEFINER
IS
BEGIN
  raise notice 'NOTICE: execute function...';
  IF n = 1 THEN                 
    RETURN n;
  ELSE
    RETURN n * factorial(n-1);  
  END IF;
END;
/
select factorial(3);
select factorial(3) from (select * from all_support_type order by id) ;
--package中有函数
CREATE OR REPLACE PACKAGE department_pkg IS
  -- Function declaration
 
  FUNCTION get_dept_info (p1 bigint,p2 bigint)
    RETURN bigint
    RESULT_CACHE stable;
 
END department_pkg;
/
CREATE OR REPLACE PACKAGE BODY department_pkg IS
  -- Function definition
  FUNCTION get_dept_info (p1 bigint,p2 bigint)
    RETURN bigint
    RESULT_CACHE stable
  IS
	sum bigint:=0;
  BEGIN
  raise notice 'NOTICE: execute function...';
	sum:=p1+p2;
    RETURN sum;
  END get_dept_info;
END department_pkg;
/
select department_pkg.get_dept_info(1,2);
select department_pkg.get_dept_info(1,2) from (select * from all_support_type order by id) ;
select department_pkg.get_dept_info(1,col_int) from (select * from all_support_type order by id) ;
create or replace function f_test(a varchar2) RETURNS NUMBER AS $$
DECLARE
  v_cnt NUMBER;
BEGIN
	raise notice 'NOTICE: execute function...';
  SELECT count(*) INTO v_cnt FROM all_support_type WHERE id<100;
  RETURN v_cnt;
END;
$$ LANGUAGE plpgsql STABLE result_cache;
/
select col_varchar,f_test(col_varchar) from (select * from all_support_type order by id) ;
--teaddown
drop table if exists all_support_type;
drop table if exists all_support_type_temp;
drop function if exists func_smallint;
drop function if exists func_smallint_cache;
drop function if exists func_smallint_nopara;
drop function if exists func_smallint_cache;
drop function if exists func_varchar_cache;
drop function if exists func_int_cache;
drop function if exists func_bigint_cache;
drop function if exists func_number_cache;
drop function if exists func_numeric_cache;
drop function if exists func_float_cache;
drop function if exists func_double_precision_cache;
drop function if exists func_char_cache;
drop function if exists func_varchar2_cache;
drop function if exists func_nvarchar2_cache;
drop function if exists func_text_cache;
drop function if exists func_bool_cache;
drop function if exists func_timestamp_cache;
drop function if exists func_smallint_cache;
drop function if exists taxi_accum ;
drop function if exists para_cache ;
drop function if exists para_cache ;
drop function if exists var_test1;
drop function if exists func_smallint_cache;
drop function if exists tran_update_func;
drop function if exists factorial(integer);
drop function if exists f_test;
drop AGGREGATE if exists pg_concat(text) cascade;
drop AGGREGATE if exists pg_concat(text,text) cascade;
drop function if exists pg_concat;
 drop function if exists pg_concat_fin;
